Database Management

UVMS uses a database to store the following data:

• Packages (configuration data)
• Inventories (operations data)
• Events (operations data)
• Alerts (operations data)
• Nodes
• Repositories
• Logins
• User Patterns
• Groups
• Roles

By default, UVMS uses the Apache Derby internal database. UVMS can also use with Oracle or MS SQL external databases.

The database can be backed up online either periodically, managed by UVMS, or triggered from the command line (see On-Line Backup and Restore). The size of the database will of course impact the time it takes to execute the backup, which in turn will influence the frequency of on-line backups. Operations data can be purged automatically. Refer to section On-line Purge.

External database

UVMS can be installed on an existing Oracle or MS SQL Server external database. Compatible versions are described in the compatibility matrix; refer to chapter "Installation Requirements".

Communication between the UVMS and the database is provided by a JDBC driver appropriate to the database. This driver is not provided with UVMS, it must be downloaded from the database provider's website.

UVMS functionality is almost identical for all three databases: Embedded, Oracle or MS SQL Server.

• Features specific to the embedded database management are available as described here.
• These features are of course for external databases, but are documented in their guides.

Remember that the use of an external database is required for a UVMS failover architecture.

Oracle Prerequisite

Create an Oracle user with full privileges.

MS SQL Server Prerequisite

Create an authenticated MS SQL Server user using the "SQL Server and Windows Authentication", method featuring all the roles.

Create a database belonging to that user.

UVMS Installation on an external database

The information necessary for a UVMS installation on an external database is the following:

• Hostname supporting the external database
• Instance name (SQL Server)
• SID of the database created for UVMS (or Oracle URL)
• Database TCP port
• Database User administrator and the password: The installation procedure creates tables in the database and as such must have administrator privileges for the database.

Minimum Rights

If you need to reduce the rights of the UVMS database user, you will find below the templates for creating a user with the minimum rights required for proper operation and installation / upgrade of the Oracle or MS SQL Server external database.

Oracle database:

-- USER SQL

CREATE USER "DBUSER" IDENTIFIED BY "dbuserpassword"

DEFAULT TABLESPACE "DBUSER_TABLESPACE"

TEMPORARY TABLESPACE "TEMP";

-- QUOTAS

ALTER USER "DBUSER" QUOTA <DBUSER_QUOTA|UNLIMITED> ON "DBUSER_TABLESPACE";

-- ROLES

--SYSTEM PRIVILEGES

GRANT CREATE SESSION TO "DBUSER" ;

GRANT CREATE TABLE TO "DBUSER" ;

GRANT CREATE SEQUENCE TO "DBUSER" ;

MS SQL Server database:

USE master;

CREATE DATABASE UVMS_DB;

GO

USE UVMS_DB;

CREATE LOGIN dbuser WITH PASSWORD = 'dbuserpassword';

CREATE USER dbuser FOR login dbuser;

GRANT ALTER ON SCHEMA::dbo TO dbuser;

GRANT CREATE PROCEDURE TO dbuser;

GRANT CREATE TABLE TO dbuser;

GRANT REFERENCES TO dbuser;

GRANT INSERT TO dbuser;

GRANT SELECT TO dbuser;

GRANT UPDATE TO dbuser;

GRANT EXECUTE TO dbuser;

GRANT DELETE TO dbuser;